﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using Org.BouncyCastle.Utilities.Collections;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using ZoneLineNew.Attributes;

namespace ZoneLineNew.Services {
    public class NPOIService {
        #region 将泛型集合中的实体导出到指定的Excel文件

        ///// <summary>
        ///// 将泛型集合中的实体导出到指定的Excel文件
        ///// </summary>
        ///// <typeparam name="T">泛型方法中的类型</typeparam>
        ///// <param name="fileName">Excel路径和文件名</param>
        ///// <param name="dataList">包含若干对象的泛型集合</param>
        ///// <param name="columnNames">实体列的中文标题集合</param>
        ///// <param name="titletext">标题名称</param>
        ///// <param name="version">Excel的版本号，规定0:2007以前版本，否则为2007及以上版本</param>
        ///// <returns>成功返回ture</returns>
        //public static bool ExportToExcel<T>(string fileName, List<T> dataList, string titletext = "报表", int version = 1) where T : class
        //{
        //    //【1】基于NPOI创建工作簿和工作表对象
        //    HSSFWorkbook hssf = new HSSFWorkbook();   //2007以下版本
        //    XSSFWorkbook xssf = new XSSFWorkbook();   //2007以上版本
        //    //根据不同的office版本创建不同的工作簿对象
        //    IWorkbook workBook = null;
        //    if (version == 0)
        //        workBook = hssf;
        //    else
        //        workBook = xssf;

        // //【2】创建工作表 ISheet sheet = workBook.CreateSheet("sheet1");//请根据实际需要把工作表的名称，变成一个参数

        // var modelTemp = dataList.First();

        // Type type = modelTemp.GetType(); PropertyInfo[] propertyinfos =
        // type.GetProperties();//获取类型的公共属性 var columnNames = new Dictionary<string, string>();

        // var ignoreDic = new Dictionary<int, PropertyInfo>(); int index = 0; foreach (var property
        // in propertyinfos) { var ignore = property.GetCustomAttribute(typeof(IgnoreAttribute)); if
        // (ignore != null) { ignoreDic.Add(index, property); index++; continue; }

        // var rlt = property.GetCustomAttribute(typeof(DgColumnAttr)); if (rlt != null) { var temp
        // = rlt as DgColumnAttr; if (temp != null) { columnNames.Add(property.Name,
        // temp.HeaderName); } } else { columnNames.Add(property.Name, property.Name); } index++; }

        // //创建合并单元格 CellRangeAddress region = new CellRangeAddress(0, 0, 0, propertyinfos.Length -
        // 1 - ignoreDic.Count); IRow rowTitle1 = sheet.CreateRow(0); for (int i = 0; i <
        // propertyinfos.Length - 1 - ignoreDic.Count; i++) { ICell cell = rowTitle1.CreateCell(i);
        // } sheet.AddMergedRegion(region);

        // ICell titleCell = rowTitle1.CreateCell(0); //创建单元格 propertyinfos[i].Name
        // titleCell.SetCellValue(titletext);// 设置行标题 SetCellStyle(workBook, titleCell);

        // //【3】循环生成列标题和设置样式 IRow rowTitle = sheet.CreateRow(1); for (int i = 0; i <
        // propertyinfos.Length; i++) { if (ignoreDic.ContainsKey(i)) { continue; } ICell cell =
        // rowTitle.CreateCell(i); //创建单元格 propertyinfos[i].Name
        // cell.SetCellValue(columnNames[propertyinfos[i].Name]);// 设置行标题 SetCellStyle(workBook,
        // cell); //设置单元格边框 SetColumnWidth(sheet, i, 35); //设置列宽 } SetCellStyle(workBook);
        // //【4】循环实体集合生成各行数据 try { for (int i = 1; i < dataList.Count + 1; i++) { IRow row =
        // sheet.CreateRow(i + 1); for (int j = 0; j < propertyinfos.Length; j++) { if
        // (ignoreDic.ContainsKey(j)) { continue; } ICell cell = row.CreateCell(j); T model =
        // dataList[i - 1]; //根据泛型找到具体化的实体对象 string value = propertyinfos[j].GetValue(model,
        // null)?.ToString();//基于反射获取实体属性值 SetCellStyle(workBook, cell); cell.SetCellValue(value);
        // //赋值 } } } catch (Exception ex) { Console.WriteLine(ex.ToString()); }

        //    //【5】保存为Excel文件
        //    using (FileStream fs = new FileStream(fileName, FileMode.Create))
        //    {
        //        workBook.Write(fs);
        //        return true;
        //    }
        //}
        /// <summary>
        /// 将泛型集合中的实体导出到指定的Excel文件
        /// </summary>
        /// <typeparam name="T">泛型方法中的类型</typeparam>
        /// <param name="fileName">Excel路径和文件名</param>
        /// <param name="dataList">包含若干对象的泛型集合</param>
        /// <param name="columnNames">实体列的中文标题集合</param>
        /// <param name="titletext">标题名称</param>
        /// <param name="version">Excel的版本号，规定0:2007以前版本，否则为2007及以上版本</param>
        /// <param name="sheetName">工作表名称</param>
        /// <returns>成功返回ture</returns>
        public static bool ExportToExcel<T>(string fileName, List<T> dataList, string titleText = "报表", int version = 1, string sheetName = "sheet1") where T : class {
            //【1】根据文件是否存在决定是创建新的文件还是打开现有文件
            IWorkbook workBook = null;
            bool fileExists = File.Exists(fileName);

            if (fileExists) {
                // 如果文件存在，则读取现有工作簿
                using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read)) {
                    if (version == 0)
                        workBook = new HSSFWorkbook(fs);   // 2007以下版本
                    else
                        workBook = new XSSFWorkbook(fs);   // 2007以上版本
                }
            } else {
                // 如果文件不存在，则创建新的工作簿
                if (version == 0)
                    workBook = new HSSFWorkbook();   // 2007以下版本
                else
                    workBook = new XSSFWorkbook();   // 2007以上版本
            }

            //【2】检查工作表是否已存在，如果存在则根据传入的名称新增工作表
            ISheet sheet = workBook.GetSheet(sheetName);
            if (sheet == null) {
                sheet = workBook.CreateSheet(sheetName); // 创建新工作表
            }

            var modelTemp = dataList.First();
            Type type = modelTemp.GetType();
            PropertyInfo[] propertyInfos = type.GetProperties(); // 获取类型的公共属性
            var columnNames = new Dictionary<string, string>();

            var ignoreDic = new Dictionary<int, PropertyInfo>();
            int index = 0;
            foreach (var property in propertyInfos) {
                var ignore = property.GetCustomAttribute(typeof(IgnoreAttribute));
                if (ignore != null) {
                    ignoreDic.Add(index, property);
                    index++;
                    continue;
                }

                var rlt = property.GetCustomAttribute(typeof(DgColumnAttr));
                if (rlt != null) {
                    var temp = rlt as DgColumnAttr;
                    if (temp != null) {
                        columnNames.Add(property.Name, temp.HeaderName);
                    }
                } else {
                    columnNames.Add(property.Name, property.Name);
                }
                index++;
            }

            // 创建合并单元格
            CellRangeAddress region = new CellRangeAddress(0, 0, 0, propertyInfos.Length - 1 - ignoreDic.Count);
            IRow rowTitle1 = sheet.CreateRow(0);
            for (int i = 0; i < propertyInfos.Length - 1 - ignoreDic.Count; i++) {
                ICell cell = rowTitle1.CreateCell(i);
            }
            sheet.AddMergedRegion(region);

            ICell titleCell = rowTitle1.CreateCell(0);   // 创建单元格
            titleCell.SetCellValue(titleText); // 设置行标题
            //SetCellStyle(workBook, titleCell);

            //【3】循环生成列标题和设置样式
            IRow rowTitle = sheet.CreateRow(1);
            for (int i = 0; i < propertyInfos.Length; i++) {
                if (ignoreDic.ContainsKey(i)) {
                    continue;
                }
                ICell cell = rowTitle.CreateCell(i);   // 创建单元格
                cell.SetCellValue(columnNames[propertyInfos[i].Name]); // 设置行标题
                //SetCellStyle(workBook, cell);           // 设置单元格边框
                SetColumnWidth(sheet, i, 35);          // 设置列宽
            }
            //SetCellStyle(workBook);

            //【4】循环实体集合生成各行数据
            try {
                for (int i = 1; i < dataList.Count + 1; i++) {
                    IRow row = sheet.CreateRow(i + 1);
                    for (int j = 0; j < propertyInfos.Length; j++) {
                        if (ignoreDic.ContainsKey(j)) {
                            continue;
                        }
                        ICell cell = row.CreateCell(j);
                        T model = dataList[i - 1];    // 根据泛型找到具体化的实体对象
                        string value = propertyInfos[j].GetValue(model, null)?.ToString(); // 基于反射获取实体属性值
                        //SetCellStyle(workBook, cell);
                        cell.SetCellValue(value);  // 赋值
                    }
                }
            } catch (Exception ex) {
                Console.WriteLine(ex.ToString());
            }

            //【5】保存为Excel文件
            using (FileStream fs = new FileStream(fileName, FileMode.Create)) {
                workBook.Write(fs);
                return true;
            }
        }

        #endregion 将泛型集合中的实体导出到指定的Excel文件

        #region 辅助方法

        private static ICellStyle style = null;

        /// <summary>
        /// 设置cell单元格边框的公共方法
        /// </summary>
        /// <param name="workBook">接口类型的工作簿，能适应不同版本</param>
        /// <param name="cell">cell单元格对象</param>
        private static void SetCellStyle(IWorkbook workBook, ICell cell) {
            if (style == null) {
                style = workBook.CreateCellStyle();

                style.BorderBottom = BorderStyle.Thin;
                style.BorderLeft = BorderStyle.Thin;
                style.BorderRight = BorderStyle.Thin;
                style.BorderTop = BorderStyle.Thin;
                style.Alignment = HorizontalAlignment.Center;//水平对齐
                style.VerticalAlignment = VerticalAlignment.Center;//垂直对齐
                                                                   //设置字体
                IFont font = workBook.CreateFont();
                font.FontName = "微软雅黑";
                font.FontHeight = 15 * 15;
                font.Color = IndexedColors.Green.Index;   //字体颜色
                                                          //font.Color =(short )FontColor .Red  ;
                style.SetFont(font);
            }

            cell.CellStyle = style;
        }

        private static void SetCellStyle(IWorkbook workBook) {
            ICellStyle style = workBook.CreateCellStyle();
            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;

            style.BorderTop = BorderStyle.Thin;
            style.Alignment = HorizontalAlignment.Center;//水平对齐
            style.VerticalAlignment = VerticalAlignment.Center;//垂直对齐
            //设置字体
            IFont font = workBook.CreateFont();
            font.FontName = "微软雅黑";
            font.FontHeight = 15 * 15;
            font.Color = IndexedColors.Green.Index;   //字体颜色
            //font.Color =(short )FontColor .Red  ;
            style.SetFont(font);
        }

        /// <summary>
        /// 设置cell单元格列宽的公共方法
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="index">第几列</param>
        /// <param name="width">具体宽度值</param>
        private static void SetColumnWidth(ISheet sheet, int index, int width) {
            sheet.SetColumnWidth(index, width * 256);
        }

        #endregion 辅助方法
    }
}
